package com.example.starttrekking;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Random;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	private static DatabaseHelper mInstance = null;
	public SQLiteDatabase myDataBase;
	private final Context myContext;
	private static final String DATABASE_NAME = "st_db.sqlite";
	private static final String DATABASE_PATH = "//data/data/com.example.starttrekking/databases/";
	private static final int DATABASE_VERSION = 2;

	public static DatabaseHelper getInstance(Context ctx) {

		// Use the application context, which will ensure that you 
		// don't accidentally leak an Activity's context.
		// See this article for more information: http://bit.ly/6LRzfx
		if (mInstance == null) {
			mInstance = new DatabaseHelper(ctx.getApplicationContext());
		}
		return mInstance;
	}

	/**
	 * Constructor should be private to prevent direct instantiation.
	 * make call to static factory method "getInstance()" instead.
	 */
	private DatabaseHelper(Context ctx) {
		super(ctx, DATABASE_PATH + DATABASE_NAME, null, DATABASE_VERSION);
		this.myContext = ctx;
	}

	public void createDatabase() throws IOException
	{
		boolean dbExist = checkDataBase();
		if(dbExist)
		{
			//Log.v("DB Exists", "db exists");
			// By calling this method here onUpgrade will be called on a
			// writeable database, but only if the version number has been
			// bumped
			//onUpgrade(myDataBase, DATABASE_VERSION_old, DATABASE_VERSION);
		}         
		boolean dbExist1 = checkDataBase();
		if(!dbExist1)
		{
			this.getReadableDatabase();
			try
			{
				this.close();    
				copyDataBase();
			}
			catch (IOException e)
			{
				throw new Error("Error copying database");
			}
		}
	}

	//Check database already exist or not
	private boolean checkDataBase()
	{
		boolean checkDB = false;
		try
		{
			String myPath = DATABASE_PATH + DATABASE_NAME;
			File dbfile = new File(myPath);
			checkDB = dbfile.exists();
		}
		catch(SQLiteException e)
		{
		}
		return checkDB;
	}

	//Copies your database from your local assets-folder to the just created empty database in the system folder

	private void copyDataBase() throws IOException
	{
		String outFileName = DATABASE_PATH + DATABASE_NAME;
		OutputStream myOutput = new FileOutputStream(outFileName);
		InputStream myInput = myContext.getAssets().open(DATABASE_NAME);
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0)
		{
			myOutput.write(buffer, 0, length);
		}
		myInput.close();
		myOutput.flush();
		myOutput.close();
	}

	//delete database
	public void db_delete()
	{
		File file = new File(DATABASE_PATH + DATABASE_NAME);
		if(file.exists())
		{
			file.delete();
			//System.out.println("delete database file.");
		}
	}

	//Open database
	public void openDatabase() throws SQLException
	{
		String myPath = DATABASE_PATH + DATABASE_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
	}

	public synchronized void closeDataBase()throws SQLException
	{
		if(myDataBase != null)
			myDataBase.close();
		super.close();
	}

	public void onCreate(SQLiteDatabase db)
	{
	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{    
		if (newVersion > oldVersion)
		{
			//Log.v("Database Upgrade", "Database version higher than old.");
			db_delete();
		}
	}

	public boolean checkMinhasTreks(int trek_id)
	{
		boolean existe = false;
		SQLiteDatabase db = this.getReadableDatabase();
		try
		{
			Cursor cursor = db.rawQuery("SELECT COUNT(id) FROM minhas_treks WHERE id=" + trek_id, null);
			if (cursor.moveToFirst()) {
				if (cursor.getInt(0) > 0)
				{
					existe = true;
				}
			}
			cursor.close();
		}
		finally
		{
			this.closeDataBase();
		}
		return existe;
	}

	public void insertMinhasTreks(int trek_id)
	{
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("id", trek_id);
		try
		{
			db.insertOrThrow("minhas_treks", null, values);
		}
		catch (SQLiteConstraintException e)
		{
			db.delete("minhas_treks", "id=" + trek_id,null);
		}
		finally
		{
			this.closeDataBase();
		}
	}

	public int getRandomTrekId()
	{
		int trek_id = 0;
		SQLiteDatabase db = this.getReadableDatabase();
		ArrayList<Integer> treks = new ArrayList<Integer>();
		try {
			Cursor cursor = db.rawQuery("SELECT t.id FROM trekking t", null);
			if (cursor.moveToFirst()) {
				do {
					treks.add(cursor.getInt(0));

				} while (cursor.moveToNext());
			}
			cursor.close();
		}
		finally
		{
			this.closeDataBase();
		}
		Random randomGenerator = new Random();
		int randomInt = randomGenerator.nextInt(treks.size());
		trek_id = treks.get(randomInt);
		return trek_id;
	}

	public Trek getDetalhesTrek(int trek_id)
	{
		SQLiteDatabase db = this.getReadableDatabase();
		Trek trek = null;
		try {
			Cursor cursor = db.rawQuery("SELECT t.id, t.nome, t.comprimento, e.nome, c.nome, maior_inclinacao, nome_arquivo FROM trekking t, estado e, cidade c WHERE t.estado = e.id AND t.cidade = c.id AND t.id=" + trek_id, null);
			if (cursor.moveToFirst()) {
				do {
					trek = new Trek(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getFloat(5), cursor.getString(6));
				} while (cursor.moveToNext());
			}
			cursor.close();
		}
		finally
		{
			this.closeDataBase();
		}

		return trek;
	}
}
